#!/usr/bin/python3
# -*- coding:utf-8 -*-
from bs4 import BeautifulSoup as bs
from time import strftime, time, sleep
import requests
import records
import json
import re
__author__ = 'bool'

class App(object):

    db = ''
    conn = ''
    # 开始
    begin = {
        'province': None,
        'city': None,
        'county': None,
        'town': None,
        'village': None
    }
    # 当前 爬取所在
    current = {
        'province': None,
        'city': None,
        'county': None,
        'town': None,
        'village': None
    }
    status = {
        'province': False,
        'city': False,
        'county': False,
        'town': False,
        'village': False
    }
    # init 初始化函数
    def __init__(self):
        # self.db = records.Database('mysql://root:root@localhost/db?charset=utf8')
        self.db = records.Database('sqlite:///db.db')
        self.conn = self.db.get_connection()

    # 返回html
    def getHtml(self, url):
        headers = {'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/52.0.2743.116 Safari/537.36'}
        proxies = {
            # 'http': 'http://183.148.148.81:9999'
        }
        try:
            resp = requests.get(url, headers=headers, proxies=proxies, timeout=(10, 5)).content.decode('gbk', 'ignore')
            return bs(resp, "html.parser")
        except:
            print(self.current)
            # 写出json
            with open("current.json", "w") as f:
                json.dump(self.current, f)
            print('运行异常: ' + url)

    # 执行
    def run(self, url, province=None, city=None, county=None, town=None, village=None):

        # 读取 json
        with open("current.json", 'r') as load_f:
            load_dict = json.load(load_f)

        # 获取省份
        begin = load_dict['province'].strip()
        # print(load_dict)
        # exit()

        # 判断json数据是否为空
        if len(begin) <= 0 or begin is None:
            self.begin['province'] = province
            self.begin['city'] = city
            self.begin['county'] = county
            self.begin['town'] = town
            self.begin['village'] = village
        else:
            self.begin['province'] = load_dict['province'].strip()
            self.begin['city'] = load_dict['city'].strip()
            self.begin['county'] = load_dict['county'].strip()
            self.begin['town'] = load_dict['town']
            self.begin['village'] = load_dict['village']
        # 执行查询
        self.getList(url)

    # 获取 省份 直辖市 列表
    def getList(self, url=None):

        soup = self.getHtml(url)
        # 选中有内容的区域
        res = soup.findAll("tr", {"class": "provincetr"})

        # 遍历 行数
        for item in res:

            # 遍历行数
            for line in item:
                # 这里获取的是 td 里面包裹的内容
                a_label = line.find("a")
                # 判断是否出现 NoneType
                if a_label is None:
                    pass
                else:
                    temp = {
                        'title': a_label.getText(),
                        'pid': 0,
                        'code': 0,
                        'href': 'http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/' + a_label['href'],
                        'upTime': time(),
                        'upDate': strftime("%Y-%m-%d %H:%M:%S"),
                        'level': 1
                    }

                    # 记录爬取位置
                    self.current['province'] =temp['title']
                    # 判断开始
                    begin = self.begin['province']
                    if begin == temp['title'] or self.status['province'] is True or begin is None:
                        self.status['province'] = True
                        urlcode = a_label['href'].replace('.html', '')
                        id = self.insertData(temp)
                        # 获取城市
                        self.getCity(temp['href'], id, urlcode)


    # 获取 城市 列表
    def getCity(self, url=None, pid=0, urlcode='11'):

        soup = self.getHtml(url)
        # 选中有内容的区域
        res = soup.findAll("tr", {"class": "citytr"})

        # 遍历 行数
        for item in res:
            #这里获取的是 td 里面包裹的内容
            line = item.findAll("td")
            # 名称 a标签
            a_label = line[1].find('a')
            # 判断是否出现 NoneType
            if a_label is None:
                pass
            else:
                temp = {
                    'title': a_label.getText(),
                    'pid': pid,
                    'code': line[0].find('a').getText(),
                    'href': 'http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/' + a_label['href'],
                    'upTime': time(),
                    'upDate': strftime("%Y-%m-%d %H:%M:%S"),
                    'level': 2
                }
                # 记录爬取位置
                self.current['city'] = temp['title']
                # 判断开始
                begin = self.begin['city']
                if begin == temp['title'] or self.status['city'] is True or begin is None:
                    self.status['city'] = True
                    id = self.insertData(temp)
                    # 获取 县区
                    self.getCounty(temp['href'], id, urlcode)

    # 获取 县区 列表
    def getCounty(self, url=None, pid=0, urlcode='11'):
        soup = self.getHtml(url)
        # 选中有内容的区域
        res = soup.findAll("tr", {"class": "countytr"})

        # 遍历 行数
        for item in res:
            #这里获取的是 td 里面包裹的内容
            line = item.findAll("td")
            # 名称
            a_label = line[1].find('a')
            # 判断是否出现 NoneType
            if a_label is None:
                pass
            else:
                temp = {
                    'title': a_label.getText(),
                    'pid': pid,
                    'code': line[0].find('a').getText(),
                    'href': 'http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/' + urlcode + '/' + a_label['href'],
                    'upTime': time(),
                    'upDate': strftime("%Y-%m-%d %H:%M:%S"),
                    'level': 3
                }
                # 记录爬取位置
                self.current['county'] = temp['title']
                # 判断开始
                begin = self.begin['county']
                if begin == temp['title'] or self.status['county'] is True or begin is None:
                    self.status['county'] = True

                    countyCode = re.findall(r'^\d{1,3}', a_label['href'])[0]
                    id = self.insertData(temp)
                    # 获取 乡镇
                    self.getTown(temp['href'], id, urlcode, countyCode)


    # 获取 乡镇 列表
    def getTown(self, url=None, pid=0, urlcode='11', countyCode='01'):
        soup = self.getHtml(url)
        # 选中有内容的区域
        res = soup.findAll("tr", {"class": "towntr"})

        # 遍历 行数
        for item in res:
            #这里获取的是 td 里面包裹的内容
            line = item.findAll("td")
            # 名称
            a_label = line[1].find('a')
            # 判断是否出现 NoneType
            if a_label is None:
                pass
            else:
                temp = {
                    'title': a_label.getText(),
                    'pid': pid,
                    'code': line[0].find('a').getText(),
                    'href': 'http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/' + urlcode + '/' + countyCode + '/' +a_label['href'],
                    'upTime': time(),
                    'upDate': strftime("%Y-%m-%d %H:%M:%S"),
                    'level': 4
                }
                # 记录爬取位置
                self.current['town'] = temp['title']
                # 判断开始
                begin = self.begin['town']
                if begin == temp['title'] or self.status['town'] is True or begin is None:
                    self.status['town'] = True
                    id = self.insertData(temp)
                    # 获取 村社区
                    self.getVillage(temp['href'], id)

    # 获取 村社区 列表
    def getVillage(self, url=None, pid=0):
        soup = self.getHtml(url)
        # 选中有内容的区域
        res = soup.findAll("tr", {"class": "villagetr"})

        # 遍历 行数
        for item in res:
            #这里获取的是 td 里面包裹的内容
            line = item.findAll("td")
            temp = {
                'title': line[2].getText(),
                'pid': pid,
                'code': line[0].getText(),
                'href': url,
                'upTime': time(),
                'upDate': strftime("%Y-%m-%d %H:%M:%S"),
                'level': 5
            }
            # 记录爬取位置
            self.current['village'] = temp['title']
            self.insertData(temp)

    # 查询一条
    def findOne(self, temp):
        # pid 为 None 给默认值
        if temp['pid'] is None:
            temp['pid'] = 0
        params = {
            'href': temp['href'],
            'pid': str(temp['pid']),
            'code': str(temp['code']),
            'level': str(temp['level'])
        }
        rows = self.conn.query("select * from main where href = :href and pid = :pid and code = :code and level = :level", **params)
        # if temp['level'] == 1:
        #     rs = rows.all(as_dict=True)
        #     print(rs)
        return rows.first(as_dict=True)

    # 插入数据
    def insertData(self, temp):
        # pid 为 None 给默认值
        row = self.findOne(temp)
        if row is None:
            value = {
                'title': temp['title'],
                'pid': temp['pid'],
                'code': temp['code'],
                'href': temp['href'],
                'upTime': temp['upTime'],
                'upDate': temp['upDate'],
                'level': temp['level']
            }
            # 插入
            self.conn.query('INSERT INTO main(title, pid, code, href, upTime, `upDate`, `level`) values (:title, :pid, :code, :href, :upTime, :upDate, :level)', **value)
            row = self.findOne(temp)
            print("id:" + str(row['id']) + ' - ' + temp['title'] + " 级别:[" + str(temp['level']) + "] --------> 插入成功")
            return row['id']
        else:
            print("id:" + str(row['id']) + ' - ' + row['title'] + " 级别:[" + str(row['level']) + "] --------> 已经存在")
            return row['id']

if __name__ == '__main__':
    api = App()
    # api.run('http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/index.html')
    api.run('http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/index.html')